﻿using System.IO;
using System.Text;
using iTeamyou.LTS.Core.Models;
using iTeamyou.LTS.Core.Services;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.FileSystemGlobbing;
using NLog;
using Renci.SshNet;
using Spire.Pdf.Exporting.XPS.Schema;

namespace iTeamyou.LTS.DataJob
{
    public class FileSync
    {
        private const string AMASTER = "sc_amaster";
        private const string FSINFO = "sc_fsinfo";
        private const string VMASTER = "sc_vmaster";
        private const string WMS = "receiving_detail_lts";
        private const string SPLIT = "|";
        private const string CSVSplit = ",";
        private const string CREATEDBY = "SyncDB";

        public ProductService _productService;
        public SupplierService _supplierService;
        public BatchService _batchService;
        public SyncService _syncService;
        public List<SyncProduct> Products;
        public List<SyncSupplier> Suppliers;
        public List<SyncBarcode> ProductBarcodes;
        public List<SyncBatch> Batches;
        public Logger _logger;

        public FileSync(ProductService productService, SupplierService supplierService, BatchService batchService, Logger logger, SyncService syncService)
        {
            Products = new List<SyncProduct>();
            Suppliers = new List<SyncSupplier>();
            ProductBarcodes = new List<SyncBarcode>();
            Batches = new List<SyncBatch>();
            _productService = productService;
            _supplierService = supplierService;
            _batchService = batchService;
            _logger = logger;
            _syncService = syncService;
        }

        /// <summary>
        /// 同步
        /// </summary>
        /// <param name="mode">File or DB, File时读取文件写入同步表, DB时读取同步表写入业务表</param>
        /// <param name="type">当mode为DB时有效</param>
        /// <param name="path">当mode为File时有效，读取文件路径</param>
        /// <param name="bakPath">当mode为File时有效，备份文件路径</param>
        public void DownloadFile(string host, string username, string password, string remoteFilePath, string localFilePath, string? time_key)
        {
            // 创建认证方法列表：密码认证 + 交互式认证
            var authMethods = new AuthenticationMethod[]
            {
            new PasswordAuthenticationMethod(username, password),
            new KeyboardInteractiveAuthenticationMethod(username)
            };

            // 配置连接信息
            var connectionInfo = new ConnectionInfo(
                host,
                username,
                authMethods
            );

            // 绑定交互式认证事件处理
            var keyboardInteractive = (KeyboardInteractiveAuthenticationMethod)authMethods[1];
            keyboardInteractive.AuthenticationPrompt += (sender, e) =>
            {
                foreach (var prompt in e.Prompts)
                {
                    // 根据服务器提示动态响应（例如二次验证码、OTP）
                    if (prompt.Request.ToLower().Contains("password"))
                    {
                        // 如果服务器在交互式流程中再次要求密码，使用原有密码
                        prompt.Response = password;
                    }
                    else
                    {
                        // 其他提示（如验证码），从控制台读取用户输入
                        Console.Write($"服务器要求输入 [{prompt.Request}]：");
                        prompt.Response = Console.ReadLine();
                    }
                }
            };
            Console.WriteLine("尝试连接服务器。。。");
            // 创建 SFTP 客户端并连接
            using (var sftp = new SftpClient(connectionInfo))
            {
                try
                {
                    sftp.Connect();
                    Console.WriteLine("成功连接到服务器。");

                    // 获取目录下所有文件
                    var files = sftp.ListDirectory(remoteFilePath);
                    string todayStr;
                    if (string.IsNullOrWhiteSpace(time_key))
                    {
                        todayStr = DateTime.Now.ToString("yyyyMMdd");
                    }
                    else
                    {
                        todayStr = time_key;
                    }
                        

                    foreach (var file in files)
                    {
                        if (file.IsRegularFile && file.Name.Contains(todayStr))//是否为常规文件且包含**日期
                        {
                            string remoteFilePaths = $"{remoteFilePath}/{file.Name}";
                            string localFilePaths = $"{localFilePath}\\{file.Name}";
                            Console.WriteLine($"匹配文件：[{remoteFilePaths}]");
                            // 检查远程文件是否存在
                            if (!sftp.Exists(remoteFilePaths))
                            {
                                Console.WriteLine("远程文件不存在。");
                                continue;
                            }
                            // 下载文件到本地
                            using (var fileStream = System.IO.File.OpenWrite(localFilePaths))
                            {
                                Console.WriteLine("开始下载文件...");
                                sftp.DownloadFile(remoteFilePaths, fileStream);
                                Console.WriteLine("文件下载完成！");
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"发生错误: {ex.Message}");
                }
                finally
                {
                    if (sftp.IsConnected)
                        sftp.Disconnect();
                }
            }
        }

        ///补数时，type就是补数日期
        public void Sync(string mode, string? type = null, string? path = null, string? bakPath = null)
        {
            bool mo = mode.ToLower() == "file";
            bool pa = Directory.Exists(path);
            bool bak = Directory.Exists(bakPath);
            if (mode.ToLower() == "file" && Directory.Exists(path) && Directory.Exists(bakPath))
            {
                Console.WriteLine("开始同步数据到数据库");
                SyncFromFile(path, bakPath, type);
                Console.WriteLine("开始更新正式数据库");
                SyncFromDB(type);
            }   
            else
                return;
        }

        public void SyncFromFile(string path, string bakPath, string type)
        {
            _logger.Debug($"Sync File Start: path:{path}, bakPath:{bakPath}");
            Console.WriteLine($"Sync File Start: path:{path}, bakPath:{bakPath}");

            ReadFile(path, bakPath, type);

            _logger.Debug($"Sync File End");
            Console.WriteLine($"Sync File End");
        }

        /*public void SyncFromDB(string type)
        {
            _logger.Debug($"Sync DB Start: type:{type}");
            Console.WriteLine($"Sync DB Start: type:{type}");
            var createdOn = DateTime.Now;

            switch (type.ToLower())
            {
                case "supplierupdate":
                    Suppliers = _syncService.GetSyncSuppliers();
                    _logger.Debug($"SupplierUpdate Count: {Suppliers.Count}");
                    if (Suppliers.Any())
                    {
                        foreach (var supplier in Suppliers)
                        {
                            var existSuppliersByCode = _supplierService.GetByCode(supplier.SId);
                            if (existSuppliersByCode.Any())
                            {
                                var exist = existSuppliersByCode.FirstOrDefault();
                                exist.RefId = supplier.RefId;
                                exist.SId = supplier.SId;
                                exist.LastModifiedBy = CREATEDBY;
                                exist.LastModifiedOn = createdOn;
                                _supplierService.Update(exist);
                                //_syncService.Update(supplier);
                            }
                        }
                    }
                    break;
                case "supplier":
                    Suppliers = _syncService.GetSyncSuppliers();
                    _logger.Debug($"Suppliers Count: {Suppliers.Count}");
                    if (Suppliers.Any())
                    {
                        foreach (var supplier in Suppliers)
                        {
                            var existSuppliersByCode = _supplierService.GetByCode(supplier.SId);
                            var existSuppliersByName = _supplierService.GetByName(supplier.Name);

                            //当SAP推送供应商数据到LTS时，LTS无此供应商编码的记录，且无匹配到无编码的供应商名称:系统自动新建一条数据
                            if (!existSuppliersByCode.Any() && !existSuppliersByName.Any())
                            {
                                var newSupplier = new Supplier()
                                {
                                    Id = Guid.NewGuid().ToString("N"),
                                    Contact = supplier.Contact,
                                    ContactNumber = supplier.ContactNumber,
                                    CreatedBy = CREATEDBY,
                                    CreatedOn = createdOn,
                                    LastModifiedBy = CREATEDBY,
                                    LastModifiedOn = createdOn,
                                    Name = supplier.Name,
                                    RefId = supplier.RefId,
                                    Region = supplier.Region,
                                    SId = supplier.SId,
                                    Status = Status.InCooperation
                                };
                                _supplierService.Save(newSupplier);
                                _syncService.Update(supplier);
                            }
                            //当SAP推送供应商数据到LTS时，且LTS已存在该供应商编码:更新对应供应商编码的记录数据
                            else if (existSuppliersByCode.Any())
                            {
                                var exist = existSuppliersByCode.FirstOrDefault();
                                exist.Name = supplier.Name;
                                exist.RefId = supplier.RefId;
                                exist.Contact = supplier.Contact;
                                exist.ContactNumber = supplier.ContactNumber;
                                //exist.Status = Status.InCooperation;
                                exist.LastModifiedBy = CREATEDBY;
                                exist.LastModifiedOn = createdOn;
                                _supplierService.Update(exist);
                                _syncService.Update(supplier);
                            }
                            //当SAP推送供应商数据到LTS时，LTS无此供应商编码的记录，但可以匹配到无编码的供应商名称，且只匹配到一条记录:更新对应供应商名称的记录数据（写入编码及其他信息）
                            else if (existSuppliersByName.Count == 1)
                            {
                                var exist = existSuppliersByName.FirstOrDefault();
                                exist.SId = supplier.SId;
                                exist.RefId = supplier.RefId;
                                exist.Name = supplier.Name;
                                exist.Contact = supplier.Contact;
                                exist.ContactNumber = supplier.ContactNumber;
                                //exist.Status = Status.InCooperation;
                                exist.LastModifiedBy = CREATEDBY;
                                exist.LastModifiedOn = createdOn;
                                _supplierService.Update(exist);
                                _syncService.Update(supplier);
                            }
                            //当SAP推送供应商数据到LTS时，LTS无此供应商编码的记录，但可以匹配到无编码的供应商名称，且匹配到多条记录:不更新，系统邮件通知业务，由业务手工更新编码
                            else if (existSuppliersByName.Count > 1)
                            {
                                //todo:Email
                            }
                        }
                    }
                    break;
                case "product":
                    Products = _syncService.GetSyncProducts();
                    ProductBarcodes = _syncService.GetSyncBarcodes();
                    if (Products.Any())
                    {
                        foreach (var product in Products)
                        {
                            var barcode = ProductBarcodes.Where(p => p.PId == product.PId);
                            if (barcode != null && barcode.Any())
                                product.UPC = barcode.FirstOrDefault().UPC;

                            if (!string.IsNullOrEmpty(product.UPC))
                            {
                                var newProduct = new Product();
                                var existProductsByCode = _productService.GetBySKU(product.PId);
                                var existProductsByUPC = _productService.GetByBarcode(product.UPC);
                                //product.SupplierId = _supplierService.GetByCode(product.SupplierId)?.FirstOrDefault()?.Id;
                                newProduct.SupplierId = _supplierService.GetByRefId(product.SupplierRefId)?.FirstOrDefault()?.Id;

                                //当SAP推送商品数据到LTS时，LTS无此商品编码的记录，且无匹配到无编码的商品条码
                                if (existProductsByCode == null && existProductsByUPC == null)
                                {
                                    newProduct.Id = Guid.NewGuid().ToString("N");
                                    newProduct.LastModifiedBy = CREATEDBY;
                                    newProduct.LastModifiedOn = createdOn;
                                    newProduct.CreatedBy = CREATEDBY;
                                    newProduct.CreatedOn = createdOn;
                                    newProduct.Name = product.Name;
                                    newProduct.OriginPlace = product.OriginPlace;
                                    newProduct.PId = product.PId;
                                    newProduct.SectionId = product.SectionId;
                                    newProduct.Specification = product.Specification;
                                    //newProduct.SupplierRefId = product.SupplierRefId;
                                    newProduct.UPC = product.UPC;
                                    _productService.Save(newProduct);
                                    _syncService.Update(product);
                                }
                                //当SAP推送商品数据到LTS时，且LTS已存在该商品编码
                                else if (existProductsByCode != null)
                                {
                                    existProductsByCode.UPC = product.UPC;
                                    existProductsByCode.Name = product.Name;
                                    existProductsByCode.Specification = product.Specification;
                                    //existProductsByCode.Brand = product.Brand;
                                    //existProductsByCode.Department = product.Department;
                                    //existProductsByCode.Category = product.Category;
                                    existProductsByCode.SectionId = product.SectionId;
                                    existProductsByCode.OriginPlace = product.OriginPlace;
                                    existProductsByCode.LastModifiedBy = CREATEDBY;
                                    existProductsByCode.LastModifiedOn = createdOn;
                                    existProductsByCode.SupplierId = newProduct.SupplierId;
                                    _productService.Update(existProductsByCode);
                                    _syncService.Update(product);
                                }
                                //当SAP推送商品数据到LTS时，LTS无此商品编码的记录，但可以匹配到无编码的商品条码
                                else if (existProductsByUPC != null && string.IsNullOrEmpty(existProductsByUPC.PId))
                                {
                                    existProductsByUPC.PId = product.PId;
                                    existProductsByUPC.Name = product.Name;
                                    existProductsByUPC.Specification = product.Specification;
                                    //existProductsByUPC.Brand = product.Brand;
                                    //existProductsByUPC.Department = product.Department;
                                    //existProductsByUPC.Category = product.Category;
                                    existProductsByUPC.SectionId = product.SectionId;
                                    existProductsByUPC.OriginPlace = product.OriginPlace;
                                    existProductsByUPC.LastModifiedBy = CREATEDBY;
                                    existProductsByUPC.LastModifiedOn = createdOn;
                                    existProductsByUPC.SupplierId = newProduct.SupplierId;
                                    _productService.Update(existProductsByUPC);
                                    _syncService.Update(product);
                                }
                            }
                        }
                    }
                    break;
                case "batch":
                    Batches = _syncService.GetSyncBatches();
                    if (Batches.Any())
                    {
                        foreach (var batch in Batches)
                        {
                            if (!string.IsNullOrEmpty(batch.ProductSKU))
                            {
                                var product = _productService.GetBySKU("111" + batch.ProductSKU.Substring(3, 6));
                                if (product != null)
                                {
                                    var newBatch = new Batch()
                                    {
                                        Id = Guid.NewGuid().ToString("N"),
                                        BId = batch.BId,
                                        Number = batch.Number,
                                        Quantity = batch.Quantity,
                                        ProductName = batch.ProductName,
                                        SupplierName = batch.SupplierName,
                                        ProductSKU = batch.ProductSKU,
                                        OrderNumber = batch.OrderNumber,
                                        EntryDatetime = batch.EntryDatetime,
                                        WarehouseNumber = batch.WarehouseNumber,
                                        DateDue = batch.DateDue,
                                        ProducedDate = batch.ProducedDate,
                                        CreatedBy = CREATEDBY,
                                        CreatedOn = createdOn,
                                        LastModifiedBy = CREATEDBY,
                                        LastModifiedOn = createdOn,
                                        ProductId = product.Id
                                    };

                                    _batchService.Save(newBatch);
                                    _syncService.Update(batch);
                                }
                            }
                        }
                    }
                    break;
                default:
                    break;
            }
            _logger.Debug("Sync DB Finish");
            Console.WriteLine($"Sync DB End");
        }*/

        //SyncFromDB优化
        public void SyncFromDB(string type)
        {
            _logger.Debug($"Sync DB Start: type:{type}");
            Console.WriteLine($"Sync DB Start: type:{type}");
            var createdOn = DateTime.Now;

            Console.WriteLine("开始更新SupplierUpdate");
            SyncSupplierUpdate(createdOn);
            Console.WriteLine("开始更新Suppliers");
            SyncSupplier(createdOn);
            Console.WriteLine("开始更新products");
            SyncProduct(createdOn);
            Console.WriteLine("开始更新batches");
            SyncBatch(createdOn);
            
            _logger.Debug("Sync DB Finish");
            Console.WriteLine($"Sync DB End");
        }

        public void ReadFile(string path, string bakPath, string type)
        {
            var files = Directory.GetFiles(path);
            foreach (var file in files)
            {
                if (file.Contains(VMASTER))
                    try
                    {
                        ReadFileVMaster(file);
                    }
                    catch(Exception VMASTER)
                    {
                        Console.WriteLine("ReadFileVMaster异常：" + VMASTER.Message);
                    }
                else if (file.Contains(FSINFO))
                    try
                    {
                        ReadFileFdInfo(file);
                    }
                    catch (Exception FSINFO)
                    {
                        Console.WriteLine("ReadFileFdInfo异常：" + FSINFO.Message);
                    }
                else if (file.Contains(AMASTER))
                    try
                    {
                        ReadFileAMaster(file);
                    }
                    catch (Exception AMASTER)
                    {
                        Console.WriteLine("ReadFileAMaster异常：" + AMASTER.Message);
                    }
                else if (file.Contains(WMS))
                    try
                    {
                        ReadFileWMS(file);
                    }
                    catch (Exception WMS)
                    {
                        Console.WriteLine("ReadFileWMS异常：" + WMS.Message);
                    }

                var fileName = System.IO.Path.GetFileName(file);
                var currentBakPath = "";
                if (string.IsNullOrEmpty(type))
                {
                    currentBakPath = System.IO.Path.Combine(bakPath, DateTime.Now.ToString("yyyyMMdd"));
                }
                else
                {
                    currentBakPath = System.IO.Path.Combine(bakPath, type);
                }
                if (!Directory.Exists(currentBakPath))
                    Directory.CreateDirectory(currentBakPath);

                File.Move(file, System.IO.Path.Combine(currentBakPath, fileName));
            }
        }

        private void ReadFileVMaster(string vmasterFile)
        {
            if (!string.IsNullOrEmpty(vmasterFile) && File.Exists(vmasterFile))
            {
                var vmasterContent = File.ReadAllLines(vmasterFile).ToList();
                vmasterContent.RemoveRange(0, 1);
                vmasterContent.RemoveRange(vmasterContent.Count - 1, 1);

                var createdOn = DateTime.Now;

                foreach (var vmasterRecord in vmasterContent)
                {
                    var record = vmasterRecord.Split(SPLIT);
                    var supplier = new SyncSupplier()
                    {
                        Id = Guid.NewGuid().ToString("N"),
                        RefId = record[0],
                        SId = record[1],
                        Name = record[2],
                        Contact = record[3],
                        ContactNumber = record[4],
                        SyncFrom = CREATEDBY,
                        SyncTime = createdOn,
                        IsSynced = false
                    };
                    _syncService.Save(supplier);
                }
            }
        }

        private void ReadFileFdInfo(string fdInfoFile)
        {
            if (!string.IsNullOrEmpty(fdInfoFile) && File.Exists(fdInfoFile))
            {
                var fdInfoContent = File.ReadAllLines(fdInfoFile).ToList();
                fdInfoContent.RemoveRange(0, 1);
                fdInfoContent.RemoveRange(fdInfoContent.Count - 1, 1);
                var createdOn = DateTime.Now;
                foreach (var fdInfo in fdInfoContent)
                {
                    var record = fdInfo.Split(SPLIT);
                    var productBarcode = new SyncBarcode()
                    {
                        PId = "111" + record[0],
                        UPC = record[1],
                        SyncFrom = CREATEDBY,
                        SyncTime = createdOn,
                        IsSynced = false
                    };

                    //ProductBarcodes.Add(productBarcode);
                    _syncService.Save(productBarcode);
                }
            }
        }

        private void ReadFileAMaster(string amasterFile)
        {
            if (!string.IsNullOrEmpty(amasterFile) && File.Exists(amasterFile))
            {
                var amasterContent = File.ReadAllLines(amasterFile).ToList();
                amasterContent.RemoveRange(0, 1);
                amasterContent.RemoveRange(amasterContent.Count - 1, 1);

                var createdOn = DateTime.Now;

                foreach (var amasterRecord in amasterContent)
                {
                    var record = amasterRecord.Split(SPLIT);
                    var product = new SyncProduct()
                    {
                        Id = Guid.NewGuid().ToString("N"),
                        PId = "111" + record[0],
                        Name = record[1],
                        //SupplierId = record[2],
                        SupplierRefId = record[2],
                        OriginPlace = record[3],
                        Specification = record[5],
                        SectionId = record[12],
                        SyncFrom = CREATEDBY,
                        SyncTime = createdOn,
                        IsSynced = false
                    };

                    //var barcode = ProductBarcodes.Where(p => p.PId == product.PId);
                    //if (barcode != null && barcode.Any())
                    //    product.UPC = barcode.FirstOrDefault().UPC;

                    //Products.Add(product);
                    _syncService.Save(product);
                }
            }
        }

        private void ReadFileWMS(string wmsFile)
        {
            Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
            if (!string.IsNullOrEmpty(wmsFile) && File.Exists(wmsFile))
            {
                using var sr = new StreamReader(wmsFile, Encoding.GetEncoding("gb2312"));
                var wmsContent = new List<string>();
                string readLine;
                while ((readLine = sr.ReadLine()) != null)
                    wmsContent.Add(readLine);
                wmsContent.RemoveRange(0, 1);

                var createdOn = DateTime.Now;

                foreach (var wmsRecord in wmsContent)
                {
                    var record = wmsRecord.Split(CSVSplit);

                    var batch = new SyncBatch()
                    {
                        Id = Guid.NewGuid().ToString("N"),
                        BId = record[2],
                        Number = record[25],
                        Quantity = 0,
                        ProductName = record[8],
                        SupplierName = record[5],
                        ProductSKU = record[7],
                        OrderNumber = record[1],
                        EntryDatetime = DateTime.Parse(record[14]),
                        WarehouseNumber = record[11],
                        DateDue = DateTime.Parse(record[24]),
                        ProducedDate = DateTime.Parse(record[23]),
                        SyncFrom = CREATEDBY,
                        SyncTime = createdOn,
                        IsSynced = false
                    };

                    try
                    {
                        var quantity = int.Parse(record[12]);
                        batch.Quantity = quantity;
                    }
                    catch
                    {
                    }
                    _syncService.Save(batch);
                }
            }
        }

        private string UTF8ToGB2312(string input)
        {
            Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
            var utf8 = Encoding.GetEncoding("UTF-8");
            var gb2312 = Encoding.GetEncoding("GB2312");
            var temp = gb2312.GetBytes(input);
            return utf8.GetString(Encoding.Convert(gb2312, utf8, temp));
        }

        private void SyncSupplierUpdate(DateTime createdOn)
        {
            Suppliers = _syncService.GetSyncSuppliers();
            _logger.Debug($"SupplierUpdate Count: {Suppliers.Count}");
            if (Suppliers.Any())
            {
                // todo1. 收集所有待更新的SId
                //var sIds = Suppliers.Select(s => s.SId).Distinct().ToList();
                var sIds = Suppliers.Select(s => s.SId).ToList();
                // todo2. 批量查询已存在的供应商信息
                var existingSuppliers = _supplierService.GetByCodes(sIds);
                // 转成字典，快速匹配
                //var existingSupplierDict = existingSuppliers.ToDictionary(s => s.SId);
                var existingSuppliersByCode = existingSuppliers.GroupBy(s => s.SId).ToDictionary(g => g.Key, g => g.ToList());//去重
                var suppliersToUpdate = new List<Supplier>();
                foreach (var supplier in Suppliers)
                {
                    // 4. 查找匹配的已存在供应商
                    //if (existingSupplierDict.TryGetValue(supplier.SId, out var exist))
                    if (existingSuppliersByCode.TryGetValue(supplier.SId, out var existingList) && existingList.Count > 0)
                    {
                        // 取第一个匹配的供应商进行更新
                        var exist = existingList[0];
                        // 5. 更新字段
                        exist.RefId = supplier.RefId;
                        exist.SId = supplier.SId;
                        exist.LastModifiedBy = CREATEDBY;
                        exist.LastModifiedOn = createdOn;
                        suppliersToUpdate.Add(exist);
                    }
                }
                if (suppliersToUpdate.Any())
                {
                    _supplierService.BatchUpdate(suppliersToUpdate);
                }
            }
        }

        private void SyncSupplier(DateTime createdOn)
        {
            Suppliers = _syncService.GetSyncSuppliers();
            _logger.Debug($"Suppliers Count: {Suppliers.Count}");
            if (Suppliers.Any())
            {
                // 1. 收集所有SId和Name
                var supplierCodes = Suppliers.Select(s => s.SId).Distinct().ToList();
                var supplierNames = Suppliers.Select(s => s.Name).Distinct().ToList();
                // 2. 批量查询已存在的供应商信息
                var existingSuppliersByCode = _supplierService.GetByCodes(supplierCodes).GroupBy(s => s.SId).ToDictionary(g => g.Key, g => g.First());//去重
                var existingSuppliersByName = _supplierService.GetByNames(supplierNames);

                // 准备批量保存和更新的列表
                var suppliersToAdd = new List<Supplier>();
                var suppliersToUpdate = new List<Supplier>();
                var syncToUpdate = new List<SyncSupplier>();

                foreach (var supplier in Suppliers)
                {
                    existingSuppliersByCode.TryGetValue(supplier.SId, out var existSupByCode);
                    var existSupByName = existingSuppliersByName.FirstOrDefault(s => s.Name == supplier.Name);

                    // 1. 新建：都不存在
                    if (existSupByCode == null && existSupByName == null)
                    {
                        var newSupplier = new Supplier()
                        {
                            Id = Guid.NewGuid().ToString("N"),
                            Contact = supplier.Contact,
                            ContactNumber = supplier.ContactNumber,
                            CreatedBy = CREATEDBY,
                            CreatedOn = createdOn,
                            LastModifiedBy = CREATEDBY,
                            LastModifiedOn = createdOn,
                            Name = supplier.Name,
                            RefId = supplier.RefId,
                            Region = supplier.Region,
                            SId = supplier.SId,
                            Status = Status.InCooperation
                        };
                        suppliersToAdd.Add(newSupplier);
                        syncToUpdate.Add(supplier);
                    }
                    // 2. 只匹配到名称（无对应 SId）
                    else if (existSupByName != null && !existingSuppliersByCode.ContainsKey(supplier.SId))
                    {
                        existSupByName.SId = supplier.SId;
                        existSupByName.RefId = supplier.RefId;
                        existSupByName.Name = supplier.Name;
                        existSupByName.Contact = supplier.Contact;
                        existSupByName.ContactNumber = supplier.ContactNumber;
                        existSupByName.LastModifiedBy = CREATEDBY;
                        existSupByName.LastModifiedOn = createdOn;
                        suppliersToUpdate.Add(existSupByName);
                        syncToUpdate.Add(supplier);
                    }
                    // 3. 以 SId 为主更新（已存在对应编码）
                    else if (existSupByCode != null)
                    {
                        existSupByCode.Name = supplier.Name;
                        existSupByCode.RefId = supplier.RefId;
                        existSupByCode.Contact = supplier.Contact;
                        existSupByCode.ContactNumber = supplier.ContactNumber;
                        existSupByCode.LastModifiedBy = CREATEDBY;
                        existSupByCode.LastModifiedOn = createdOn;
                        suppliersToUpdate.Add(existSupByCode);
                        syncToUpdate.Add(supplier);
                    }
                    // 4. 多个匹配到名称，或未处理情况
                    else if (existingSuppliersByName.Count > 1)
                    {
                        // TODO: 发送邮件通知，或其他处理逻辑
                        // 这里暂时不操作
                    }
                }
                // 5. 批量插入和更新
                if (suppliersToAdd.Any())
                {
                    _supplierService.BatchSave(suppliersToAdd);
                }
                if (suppliersToUpdate.Any())
                {
                    suppliersToUpdate = suppliersToUpdate.GroupBy(s => s.Id).Select(g => g.First()).ToList();
                    _supplierService.BatchUpdate(suppliersToUpdate);
                }
                if (syncToUpdate.Any())
                {
                    _syncService.BatchUpdate(syncToUpdate);
                }

            }
        }

        private void SyncProduct(DateTime createdOn)
        {
            Products = _syncService.GetSyncProducts();//返回SyncProducts表中所有IsSynced = 0的数据
            ProductBarcodes = _syncService.GetSyncBarcodes(); //返回SyncBarcodes表中所有IsSynced = 0的数据
            if (Products.Any())
            {
                // todo1. 提取所有的PId和UPC
                var pIds = Products.Select(p => p.PId).ToList();
                var upcs = ProductBarcodes.Where(p => !string.IsNullOrEmpty(p.UPC)).Select(p => p.UPC).ToList();
                // todo2. 批量查询已存在商品
                var existingProductsBySKU = _productService.GetBySkus(pIds);
                var existingProductsByBarcode = _productService.GetByBarcodes(upcs);
                // 转换成字典，快速匹配
                var dictProductsBySKU = existingProductsBySKU.GroupBy(p => p.PId).ToDictionary(g => g.Key, g => g.First());
                var dictProductsByBarcode = existingProductsByBarcode.GroupBy(p => p.UPC).ToDictionary(g => g.Key, g => g.First());
                // todo3. 构建PId到UPC的映射
                var productBarcodesDict = ProductBarcodes.GroupBy(p => p.PId).ToDictionary(g => g.Key, g => g.First().UPC);
                // todo4. 批量获取供应商映射
                var supplierRefIds = Products.Select(p => p.SupplierRefId).Distinct().ToList();
                var suppliers = _supplierService.GetByRefIds(supplierRefIds);
                var supplierDict = suppliers.ToDictionary(s => s.RefId);
                // todo5. 准备批量插入和更新的列表
                var productsToInsert = new List<Product>();
                var productsToUpdate = new List<Product>();
                var syncProductsToUpdate = new List<SyncProduct>();
                // todo6. 遍历所有商品，进行匹配和处理
                foreach (var product in Products)
                {
                    // 获取对应的UPC
                    product.UPC = productBarcodesDict.ContainsKey(product.PId) ? productBarcodesDict[product.PId] : null;
                    // 取出已存在的商品
                    var existBySKU = dictProductsBySKU.ContainsKey(product.PId) ? dictProductsBySKU[product.PId] : null;
                    var existByUPC = !string.IsNullOrEmpty(product.UPC) && dictProductsByBarcode.ContainsKey(product.UPC) ? dictProductsByBarcode[product.UPC] : null;
                    // 获取供应商ID
                    string? supplierId = supplierDict.TryGetValue(product.SupplierRefId, out var supplier) ? supplier.Id : null;

                    // 处理逻辑
                    if (existBySKU == null && existByUPC == null)
                    {
                        // 新增商品
                        var newProduct = new Product
                        {
                            Id = Guid.NewGuid().ToString("N"),
                            LastModifiedBy = CREATEDBY,
                            LastModifiedOn = createdOn,
                            CreatedBy = CREATEDBY,
                            CreatedOn = createdOn,
                            Name = product.Name,
                            OriginPlace = product.OriginPlace,
                            PId = product.PId,
                            SectionId = product.SectionId,
                            Specification = product.Specification,
                            UPC = product.UPC,
                            SupplierId = supplierId
                        };
                        productsToInsert.Add(newProduct);
                        syncProductsToUpdate.Add(product);
                    }
                    else if (existBySKU != null)
                    {
                        // 更新已存在的商品（以SKU匹配）
                        existBySKU.UPC = product.UPC;
                        existBySKU.Name = product.Name;
                        existBySKU.Specification = product.Specification;
                        existBySKU.SectionId = product.SectionId;
                        existBySKU.OriginPlace = product.OriginPlace;
                        existBySKU.LastModifiedBy = CREATEDBY;
                        existBySKU.LastModifiedOn = createdOn;
                        existBySKU.SupplierId = supplierId;
                        productsToUpdate.Add(existBySKU);
                        syncProductsToUpdate.Add(product);
                    }
                    else if (existByUPC != null && string.IsNullOrEmpty(existByUPC.PId))
                    {
                        existByUPC.PId = product.PId;
                        existByUPC.Name = product.Name;
                        existByUPC.Specification = product.Specification;
                        existByUPC.SectionId = product.SectionId;
                        existByUPC.OriginPlace = product.OriginPlace;
                        existByUPC.LastModifiedBy = CREATEDBY;
                        existByUPC.LastModifiedOn = createdOn;
                        existByUPC.SupplierId = supplierId;
                        productsToUpdate.Add(existByUPC);
                        syncProductsToUpdate.Add(product);
                    }
                }
                // todo7. 批量更新、批量插入、批量同步
                if (productsToUpdate.Any())
                {
                    _productService.BatchUpdate(productsToUpdate);
                }
                if (productsToInsert.Any())
                {
                    _productService.BatchSave(productsToInsert);
                }
                if (syncProductsToUpdate.Any())
                {
                    _syncService.BatchUpdate(syncProductsToUpdate);
                }

            }
        }

        private void SyncBatch(DateTime createdOn)
        {
            Batches = _syncService.GetSyncBatches();
            if (Batches.Any())
            {
                // 收集所有唯一的 ProductSKU
                var productSKUS = Batches.Where(b => !string.IsNullOrEmpty(b.ProductSKU)).Select(b => "111" + b.ProductSKU.Substring(3, 6)).Distinct().ToList();
                // 批量获取产品信息
                var products = _productService.GetBySkus(productSKUS);
                var productDict = products.GroupBy(p => p.PId).ToDictionary(g => g.Key, g => g.First());

                // 准备新的批次列表
                var batchesToInsert = new List<Batch>();
                var syncBatchToUpdate = new List<SyncBatch>();

                foreach (var batch in Batches)
                {
                    if (!string.IsNullOrEmpty(batch.ProductSKU))
                    {
                        string modifiedSKU = "111" + batch.ProductSKU.Substring(3, 6);
                        if (productDict.TryGetValue(modifiedSKU, out var product))
                        {
                            var newBatch = new Batch
                            {
                                Id = Guid.NewGuid().ToString("N"),
                                BId = batch.BId,
                                Number = batch.Number,
                                Quantity = batch.Quantity,
                                ProductName = batch.ProductName,
                                SupplierName = batch.SupplierName,
                                ProductSKU = batch.ProductSKU,
                                OrderNumber = batch.OrderNumber,
                                EntryDatetime = batch.EntryDatetime,
                                WarehouseNumber = batch.WarehouseNumber,
                                DateDue = batch.DateDue,
                                ProducedDate = batch.ProducedDate,
                                CreatedBy = CREATEDBY,
                                CreatedOn = createdOn,
                                LastModifiedBy = CREATEDBY,
                                LastModifiedOn = createdOn,
                                ProductId = product.Id
                            };
                            batchesToInsert.Add(newBatch);
                            syncBatchToUpdate.Add(batch);
                        }
                    }
                }
                // 批量保存新批次
                if (batchesToInsert.Any())
                {
                    _batchService.BatchSave(batchesToInsert);
                }

                // 批量更新已同步的批次
                if (syncBatchToUpdate.Any())
                {
                    _syncService.BatchUpdate(syncBatchToUpdate);
                }
            }
            
        }
    }

    public class ProductBarcode
    {
        public string PId { get; set; }
        public string UPC { get; set; }
    }
}

